// tableCategories.js
// const sqlite3 = require('sqlite3').verbose();

class CategoriesTable {
    constructor(app, db) {
        this.app = app;
        this.db = db;
    }

    init() {
        // 创建categories表，存储categories信息
        const sqlCategoriesTable = `
        CREATE TABLE IF NOT EXISTS categories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            dataset_id TEXT NOT NULL,
            category_id INTEGER NOT NULL,
            category_name TEXT NOT NULL,
            supercategory TEXT,
            keypoints TEXT,
            skeleton TEXT,
            style TEXT
        );`;

        this.db.run(sqlCategoriesTable, (err) => {
            if (err) {
                console.error('Create "categories" Table Failed, Error: ', err);
            }
        });
    }

    // 插入数据集的类型信息
    async insertCategory(datasetId, categoryInfo, style = '') {
        // console.log(`datasetId = ${datasetId}, categoryInfo = `, categoryInfo);
        return new Promise(async (resolve, reject) => {
            try {
                let { category_id, category_name, supercategory, keypoints, skeleton } = categoryInfo;

                // Fetch the maximum category_id for the given datasetId
                if (category_id === undefined || category_id === null) {
                    const maxCategoryIdQuery = 'SELECT MAX(category_id) AS maxCategoryId FROM categories WHERE dataset_id = ?;';
                    const result = await this.db.get(maxCategoryIdQuery, [datasetId]);
                    category_id = (result.maxCategoryId || 0) + 1;
                }

                // Insert the new category
                const sqlInsertCategory = `
                    INSERT INTO categories (dataset_id, category_id, category_name, supercategory, keypoints, skeleton, style)
                    VALUES (?, ?, ?, ?, ?, ?, ?);`;

                this.db.run(sqlInsertCategory, [datasetId, category_id, category_name, supercategory, keypoints, skeleton, style], function (err) {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(this.lastID);
                    }
                });
            } catch (error) {
                reject(error);
            }
        });
    }

    // 返回某一个dataset_id的categories列表
    async getCategories(datasetId) {
        return new Promise((resolve, reject) => {
            const sqlGetCategories = `
                SELECT * FROM categories WHERE dataset_id = ?;`;
            this.db.all(sqlGetCategories, [datasetId], (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    // 删除某一个id对应的categories记录
    async deleteCategoriesByIdList(idList) {
        const placeholders = idList.map(() => '?').join(', ');
        const sqlDelete = `DELETE FROM categories WHERE dataset_id IN (${placeholders});`;

        return new Promise((resolve, reject) => {
            this.db.run(sqlDelete, idList, function (err) {
                if (err) {
                    reject({ code: -1, msg: err.message });
                } else {
                    resolve({
                        code: 0,
                        msg: `Deleted categories with IDs [${idList.join(', ')}] OK`
                    });
                }
            });
        });
    }

    // 更新category信息
    async updateCategory(category_info) {
        const { id, category_name, supercategory, keypoints, skeleton, style } = category_info;
        const params = [];
        let sqlUpdate = 'UPDATE categories SET';

        if (category_name !== undefined) {
            sqlUpdate += ' category_name = ?,';
            params.push(category_name);
        }

        if (supercategory !== undefined) {
            sqlUpdate += ' supercategory = ?,';
            params.push(supercategory);
        }

        if (keypoints !== undefined) {
            sqlUpdate += ' keypoints = ?,';
            params.push(keypoints);
        }

        if (skeleton !== undefined) {
            sqlUpdate += ' skeleton = ?,';
            params.push(skeleton);
        }

        if (style !== undefined) {
            sqlUpdate += ' style = ?,';
            params.push(style);
        }

        // Remove the trailing comma and add the WHERE clause
        sqlUpdate = sqlUpdate.slice(0, -1) + ' WHERE id = ?;';
        params.push(id);

        return new Promise((resolve, reject) => {
            this.db.run(sqlUpdate, params, function (err) {
                if (err) {
                    reject({ code: -1, msg: err.message });
                } else {
                    if (this.changes > 0) {
                        resolve({
                            code: 0,
                            msg: `Updated category with ID ${id} OK`
                        });
                    } else {
                        reject({
                            code: -2,
                            msg: `No category found with ID ${id}`
                        });
                    }
                }
            });
        });
    }
}

module.exports = { CategoriesTable };
